const mysql = require('mysql2/promise');

// 数据库连接配置 - 从环境变量获取，如果没有则使用默认值
const dbConfig = {
    host: process.env.DB_HOST || '43.143.253.188',
    port: parseInt(process.env.DB_PORT) || 3306,
    user: process.env.DB_USER || 'nextjs',
    password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
    database: process.env.DB_NAME || 'nextjs'
};

async function analyzeDatabaseSchema() {
    let connection;
    
    try {
        console.log('🔌 连接到数据库...');
        console.log(`主机: ${dbConfig.host}:${dbConfig.port}`);
        console.log(`数据库: ${dbConfig.database}`);
        console.log(`用户: ${dbConfig.user}`);
        
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功！\n');
        
        // 获取所有表名
        console.log('📋 获取数据库表列表...');
        const [tables] = await connection.execute('SHOW TABLES');
        const tableNames = tables.map(row => Object.values(row)[0]);
        console.log(`找到 ${tableNames.length} 个表:\n`);
        
        // 存储所有外键信息
        const allForeignKeys = [];
        const tableSchemas = {};
        
        // 遍历每个表，分析结构
        for (const tableName of tableNames) {
            console.log(`🔍 分析表: ${tableName}`);
            
            try {
                // 获取表的创建语句
                const [createTableResult] = await connection.execute(`SHOW CREATE TABLE \`${tableName}\``);
                const createTableSql = createTableResult[0]['Create Table'];
                
                // 获取表结构信息
                const [columnsResult] = await connection.execute(`DESCRIBE \`${tableName}\``);
                const columns = columnsResult.map(col => ({
                    field: col.Field,
                    type: col.Type,
                    null: col.Null,
                    key: col.Key,
                    default: col.Default,
                    extra: col.Extra
                }));
                
                tableSchemas[tableName] = {
                    createSql: createTableSql,
                    columns: columns
                };
                
                // 查找外键约束
                const foreignKeyMatches = createTableSql.match(/CONSTRAINT\s+`([^`]+)`\s+FOREIGN KEY\s*\(`([^`]+)`\)\s+REFERENCES\s+`([^`]+)`\s*\(`([^`]+)`\)/gi);
                
                if (foreignKeyMatches && foreignKeyMatches.length > 0) {
                    console.log(`  ⚠️  发现 ${foreignKeyMatches.length} 个外键约束:`);
                    
                    for (const match of foreignKeyMatches) {
                        // 提取详细信息
                        const constraintNameMatch = match.match(/CONSTRAINT\s+`([^`]+)`/i);
                        const columnMatch = match.match(/FOREIGN KEY\s*\(`([^`]+)`\)/i);
                        const referenceMatch = match.match(/REFERENCES\s+`([^`]+)`\s*\(`([^`]+)`\)/i);
                        
                        if (constraintNameMatch && columnMatch && referenceMatch) {
                            const constraintName = constraintNameMatch[1];
                            const columnName = columnMatch[1];
                            const referenceTable = referenceMatch[1];
                            const referenceColumn = referenceMatch[2];
                            
                            allForeignKeys.push({
                                table: tableName,
                                constraint: constraintName,
                                column: columnName,
                                referenceTable: referenceTable,
                                referenceColumn: referenceColumn
                            });
                            
                            console.log(`    - 约束名: ${constraintName}`);
                            console.log(`      字段: ${columnName} -> ${referenceTable}.${referenceColumn}`);
                        }
                    }
                } else {
                    console.log(`  ✅ 无外键约束`);
                }
                
                // 显示表的基本信息
                const primaryKeys = columns.filter(col => col.key === 'PRI').map(col => col.field);
                const indexes = columns.filter(col => col.key === 'MUL' || col.key === 'UNI').map(col => col.field);
                
                console.log(`  📊 表结构信息:`);
                console.log(`     - 字段数量: ${columns.length}`);
                console.log(`     - 主键: ${primaryKeys.length > 0 ? primaryKeys.join(', ') : '无'}`);
                console.log(`     - 索引: ${indexes.length > 0 ? indexes.join(', ') : '无'}`);
                
            } catch (error) {
                console.log(`  ❌ 分析表 ${tableName} 时出错: ${error.message}`);
            }
            
            console.log(''); // 空行分隔
        }
        
        // 生成删除外键的SQL脚本
        console.log('📝 生成删除外键约束的SQL脚本...\n');
        
        if (allForeignKeys.length > 0) {
            console.log('-- 删除外键约束的SQL脚本');
            console.log('-- 执行前请备份数据库！\n');
            
            for (const fk of allForeignKeys) {
                console.log(`-- 删除表 ${fk.table} 中的外键约束 ${fk.constraint}`);
                console.log(`-- 该约束关联字段: ${fk.column} -> ${fk.referenceTable}.${fk.referenceColumn}`);
                console.log(`ALTER TABLE \`${fk.table}\` DROP FOREIGN KEY \`${fk.constraint}\`;`);
                console.log('');
            }
            
            console.log(`-- 总共需要删除 ${allForeignKeys.length} 个外键约束`);
        } else {
            console.log('🎉 数据库中没有外键约束，无需生成删除脚本！');
        }
        
        // 保存详细报告到文件
        const reportData = {
            timestamp: new Date().toISOString(),
            database: dbConfig.database,
            host: dbConfig.host,
            totalTables: tableNames.length,
            foreignKeys: allForeignKeys,
            tableSchemas: tableSchemas
        };
        
        const fs = require('fs');
        const reportFileName = `database-schema-analysis-${new Date().toISOString().replace(/[:.]/g, '-')}.json`;
        fs.writeFileSync(reportFileName, JSON.stringify(reportData, null, 2));
        console.log(`\n💾 详细分析报告已保存到: ${reportFileName}`);
        
    } catch (error) {
        console.error('❌ 操作失败:', error.message);
        if (error.code) {
            console.error('错误代码:', error.code);
        }
    } finally {
        if (connection) {
            await connection.end();
            console.log('\n🔌 数据库连接已关闭');
        }
    }
}

// 显示环境变量信息
console.log('🌍 环境变量信息:');
console.log(`DB_HOST: ${process.env.DB_HOST || '未设置 (使用默认值)'}`);
console.log(`DB_PORT: ${process.env.DB_PORT || '未设置 (使用默认值)'}`);
console.log(`DB_USER: ${process.env.DB_USER || '未设置 (使用默认值)'}`);
console.log(`DB_PASS: ${process.env.DB_PASS ? '***已设置***' : '未设置 (使用默认值)'}`);
console.log(`DB_NAME: ${process.env.DB_NAME || '未设置 (使用默认值)'}`);
console.log('');

// 运行主函数
analyzeDatabaseSchema().catch(console.error); 